package com.sf.osms.dep.processor.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Table;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.sf.osms.dep.domain.Router;

@Component
public class JdbcUtils {
	
	public static DataSource dataSource = null;
	
	public static void saveBatch(List routers) throws Exception{
		if(CollectionUtil.isEmpty(routers)){
			return;
		}
		Object next = routers.get(0);
		String sql = constructBatchSql(next.getClass());
		Connection connection = null;
		boolean autoCommit = true;
		try {
			connection = dataSource.getConnection();
			autoCommit = connection.getAutoCommit();
			connection.setAutoCommit(false);
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			for(int i=0;i<routers.size();i++){
				fillBatchSqlValues(prepareStatement,routers.get(i));
				prepareStatement.addBatch();
				if((i+1) % 10000 == 0){
					prepareStatement.executeBatch();
					connection.commit();
					prepareStatement.clearBatch();
				}
			}
			
			prepareStatement.executeBatch();
			connection.commit(); 
		} catch (Exception e) {
			throw e;
		}finally{
			
			connection.setAutoCommit(autoCommit);
			connection.close();
		}
	}
	
	public static void fillBatchSqlValues(PreparedStatement ps,Object obj) throws Exception{
		Field[] fields = obj.getClass().getDeclaredFields();
		List<Object> listValues = new ArrayList<Object>();
		for(int i=0;i<fields.length;i++){
			Field field = fields[i];
			Column column = field.getDeclaredAnnotation(Column.class);
			if(column != null && !"id".equalsIgnoreCase(column.name()) && !"insert_time".equalsIgnoreCase(column.name()) && !"status_dt".equalsIgnoreCase(column.name())){
				field.setAccessible(true);
				Object fieldValue = field.get(obj);
				listValues.add(fieldValue);
			}
		}
		
		if(!CollectionUtil.isEmpty(listValues)){
			for(int i=0;i<listValues.size();i++){
				Object fieldValue = listValues.get(i);
				ps.setObject((i+1), fieldValue);
			}
		}
	}
	
	public static String constructBatchSql(Class clazz){
		String sql = "";
		String tableName = null;
		Table table = (Table) clazz.getDeclaredAnnotation(Table.class);
		if(table != null){
			tableName = table.name(); 
		}else{
			tableName = clazz.getSimpleName();
		}
		sql = sql + "INSERT INTO " + tableName + " ";
		StringBuffer columns = new StringBuffer();
		StringBuffer properties = new StringBuffer();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			Column column = field.getDeclaredAnnotation(Column.class);
			if(column != null && !"id".equalsIgnoreCase(column.name())){
				columns.append("," + column.name());
				if("insert_time".equalsIgnoreCase(column.name()) || "status_dt".equalsIgnoreCase(column.name())){
					properties.append(",NOW()");
				}else{
					properties.append(",?");
				}
				
			}
		}
		
		String columnsStr = " (" + columns.toString().substring(columns.toString().indexOf(",")+1) + ") ";
		String propertiesStr = " (" + properties.toString().substring(properties.toString().indexOf(",")+1) + ") ";
		sql = sql + columnsStr + " VALUES " + propertiesStr;
		
		return sql;
	}
	
	
	public static void main(String[] args) throws Exception{
		Router obj = new Router();
		obj.setSfWaybill("dd");
		Field[] fields = obj.getClass().getDeclaredFields();
		for(int i=0;i<fields.length;i++){
			Field field = fields[i];
			Column column = field.getDeclaredAnnotation(Column.class);
			if(column != null && !"id".equalsIgnoreCase(column.name())){
				field.setAccessible(true);
				Object fieldValue = field.get(obj);
				System.out.println(field.getName() + ":" + fieldValue);
			}
		}
	}
	
	@Autowired
	public void setDataSource(DataSource ds){
		dataSource = ds;
	}
}
